﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class PhysicalRequestDA : IPhysicalRequestDA
    {
        private string AREA_ID_END = "@AREA_ID_END";
        private string AREA_ID_START = "@AREA_ID_START";
        private string CREATED_BY = "@CREATED_BY";
        private string DESCR = "@DESCR";
        private string LOC_END = "@LOC_END";
        private string LOC_LEVEL_END = "@LOC_LEVEL_END";
        private string LOC_LEVEL_START = "@LOC_LEVEL_START";
        private string LOC_START = "@LOC_START";
        private string ONLY_NON_EMPTY_LOC = "@ONLY_NON_EMPTY_LOC";
        private string OWNER_ID_END = "@OWNER_ID_END";
        private string OWNER_ID_START = "@OWNER_ID_START";
        private string PHYSICAL_REQUEST_ID = "@PHYSICAL_REQUEST_ID";
        private string PLAN_END_TIME = "@PLAN_END_TIME";
        private string PLAN_START_TIME = "@PLAN_START_TIME";
        private string SHOW_SYSTEM_QTY = "@SHOW_SYSTEM_QTY";
        private string SKU_ID_END = "@SKU_ID_END";
        private string SKU_ID_START = "@SKU_ID_START";
        private string SQL_CHECK_PHYSICAL_REQUEST_ID_UNIQUE = " SELECT COUNT(1) FROM @_@PHYSICAL_REQUEST WHERE PHYSICAL_REQUEST_ID = @PHYSICAL_REQUEST_ID ";
        private string SQL_DELETE_PHYSICAL_REQUEST = " DELETE FROM @_@PHYSICAL_REQUEST WHERE PHYSICAL_REQUEST_ID = @PHYSICAL_REQUEST_ID ";
        private string SQL_INSERT_PHYSICAL_REQUEST = " INSERT INTO @_@PHYSICAL_REQUEST ( WH_ID, PHYSICAL_REQUEST_ID, DESCR, OWNER_ID_START, OWNER_ID_END, SKU_ID_START, SKU_ID_END, LOC_START, LOC_END, ZONE_ID_START, ZONE_ID_END, AREA_ID_START, AREA_ID_END, LOC_LEVEL_START, LOC_LEVEL_END, SHOW_SYSTEM_QTY, STARTING_TAG_NO, TAGS_PER_GROUP, ONLY_NON_EMPTY_LOC, PLAN_START_TIME, PLAN_END_TIME, STATUS, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @WH_ID, @PHYSICAL_REQUEST_ID, @DESCR, @OWNER_ID_START, @OWNER_ID_END, @SKU_ID_START, @SKU_ID_END, @LOC_START, @LOC_END, @ZONE_ID_START, @ZONE_ID_END, @AREA_ID_START, @AREA_ID_END, @LOC_LEVEL_START, @LOC_LEVEL_END, @SHOW_SYSTEM_QTY, @STARTING_TAG_NO, @TAGS_PER_GROUP, @ONLY_NON_EMPTY_LOC, @PLAN_START_TIME, @PLAN_END_TIME, @STATUS, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_SELECT_ALL_PHYSICAL_REQUEST = " SELECT  PR.ROWID, PR.WH_ID, PR.PHYSICAL_REQUEST_ID, PR.DESCR, PR.OWNER_ID_START, PR.OWNER_ID_END, PR.SKU_ID_START, PR.SKU_ID_END, PR.LOC_START, PR.LOC_END, PR.ZONE_ID_START, PR.ZONE_ID_END, PR.AREA_ID_START, PR.AREA_ID_END, PR.LOC_LEVEL_START, PR.LOC_LEVEL_END, PR.SHOW_SYSTEM_QTY, PR.STARTING_TAG_NO, PR.TAGS_PER_GROUP, PR.ONLY_NON_EMPTY_LOC, PR.PLAN_START_TIME, PR.PLAN_END_TIME, T.[TEXT] AS STATUS_D,PR.CREATED_BY, PR.CREATED_DATE, PR.UPDATED_BY, PR.UPDATED_DATE,PR.STATUS FROM @_@PHYSICAL_REQUEST PR LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = PR.STATUS AND SCD.SYS_CODE_TYPE = 'PCStatus' LEFT JOIN sysadm.TRANSLATION T ON 'PCStatus.'+PR.STATUS = T.ITEM_ID  AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY =@LANGUAGE ";
        private string SQL_SELECT_INV_LOT_LOC_LPN_BY_PHYSICAL_REQUEST = "SELECT INV.* ,S.PACK_ID,P.PACK_UOM1 FROM  @_@INV_LOT_LOC_LPN INV INNER JOIN @_@LOCATION L ON INV.LOC= L.LOC  LEFT JOIN @_@AREA_DETAIL A ON L.ZONE_ID=A.ZONE_ID INNER JOIN @_@SKU S ON INV.OWNER_ID=S.OWNER_ID AND INV.SKU_ID=S.SKU_ID INNER JOIN @_@PACK P ON S.PACK_ID=P.PACK_ID INNER JOIN SYSADM.USER_OWNER UO ON   UO.OWNER_ID=INV.OWNER_ID AND UO.USER_ID='@USER_ID'  ";
        private string SQL_UPDATE_PHYSICAL_REQUEST = " UPDATE @_@PHYSICAL_REQUEST SET WH_ID = @WH_ID, DESCR = @DESCR, OWNER_ID_START = @OWNER_ID_START, OWNER_ID_END = @OWNER_ID_END, SKU_ID_START = @SKU_ID_START, SKU_ID_END = @SKU_ID_END, LOC_START = @LOC_START, LOC_END = @LOC_END, ZONE_ID_START = @ZONE_ID_START, ZONE_ID_END = @ZONE_ID_END, AREA_ID_START = @AREA_ID_START, AREA_ID_END = @AREA_ID_END, LOC_LEVEL_START = @LOC_LEVEL_START, LOC_LEVEL_END = @LOC_LEVEL_END, SHOW_SYSTEM_QTY = @SHOW_SYSTEM_QTY, STARTING_TAG_NO = @STARTING_TAG_NO, TAGS_PER_GROUP = @TAGS_PER_GROUP, ONLY_NON_EMPTY_LOC = @ONLY_NON_EMPTY_LOC, PLAN_START_TIME = @PLAN_START_TIME, PLAN_END_TIME = @PLAN_END_TIME, STATUS = @STATUS, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE PHYSICAL_REQUEST_ID = @PHYSICAL_REQUEST_ID ";
        private string SQL_UPDATE_PHYSICAL_REQUEST_STATUS_BY_PHYSICAL_REQUEST_ID = " UPDATE @_@PHYSICAL_REQUEST SET  STATUS=@STATUS  WHERE PHYSICAL_REQUEST_ID = @PHYSICAL_REQUEST_ID ";
        private string STARTING_TAG_NO = "@STARTING_TAG_NO";
        private string STATUS = "@STATUS";
        private string TAGS_PER_GROUP = "@TAGS_PER_GROUP";
        private string UPDATED_BY = "@UPDATED_BY";
        private string WH_ID = "@WH_ID";
        private string ZONE_ID_END = "@ZONE_ID_END";
        private string ZONE_ID_START = "@ZONE_ID_START";

        public bool CheckPhysicalRequestIDUnique(string physicalRequestID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PHYSICAL_REQUEST_ID", physicalRequestID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_PHYSICAL_REQUEST_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckPhysicalRequestIDUnique(DataBase dataBase, DbTransaction tran, string physicalRequestID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PHYSICAL_REQUEST_ID", physicalRequestID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_PHYSICAL_REQUEST_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeletePhysicalRequest(List<string> physicalRequestID, string whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int j = 0; j < physicalRequestID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PHYSICAL_REQUEST_ID", physicalRequestID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_PHYSICAL_REQUEST.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeletePhysicalRequest(DataBase dataBase, DbTransaction tran, List<string> physicalRequestID, string whLoginID)
        {
            int result = 0;
            try
            {
                for (int j = 0; j < physicalRequestID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PHYSICAL_REQUEST_ID", physicalRequestID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_PHYSICAL_REQUEST.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllPhysicalRequest(string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_PHYSICAL_REQUEST.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetAllPhysicalRequest(DataBase dataBase, DbTransaction tran, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_PHYSICAL_REQUEST.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql, paras);
        }

        public PhysicalRequestInfo GetPhysicalRequestByID(string physicalrequestID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_PHYSICAL_REQUEST.Replace("@_@", whLoginID) + " WHERE PHYSICAL_REQUEST_ID = @PHYSICAL_REQUEST_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PHYSICAL_REQUEST_ID", physicalrequestID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            PhysicalRequestInfo physicalRequestInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    physicalRequestInfo = new PhysicalRequestInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["PHYSICAL_REQUEST_ID"].ToString(), reader["DESCR"].ToString(), reader["OWNER_ID_START"].ToString(), reader["OWNER_ID_END"].ToString(), reader["SKU_ID_START"].ToString(), reader["SKU_ID_END"].ToString(), reader["LOC_START"].ToString(), reader["LOC_END"].ToString(), reader["ZONE_ID_START"].ToString(), reader["ZONE_ID_END"].ToString(), reader["AREA_ID_START"].ToString(), reader["AREA_ID_END"].ToString(), (reader["LOC_LEVEL_START"].ToString() != "") ? int.Parse(reader["LOC_LEVEL_START"].ToString()) : 0, (reader["LOC_LEVEL_END"].ToString() != "") ? int.Parse(reader["LOC_LEVEL_END"].ToString()) : 0, reader["SHOW_SYSTEM_QTY"].ToString(), (reader["STARTING_TAG_NO"].ToString() != "") ? int.Parse(reader["STARTING_TAG_NO"].ToString()) : 0, (reader["TAGS_PER_GROUP"].ToString() != "") ? int.Parse(reader["TAGS_PER_GROUP"].ToString()) : 0, reader["ONLY_NON_EMPTY_LOC"].ToString(), (reader["PLAN_START_TIME"].ToString() != "") ? DateTime.Parse(reader["PLAN_START_TIME"].ToString()) : (CS00003 = new DateTime()), (reader["PLAN_END_TIME"].ToString() != "") ? DateTime.Parse(reader["PLAN_END_TIME"].ToString()) : (CS00003 = new DateTime()), reader["STATUS"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), reader["STATUS_D"].ToString());
                }
            }
            return physicalRequestInfo;
        }

        public PhysicalRequestInfo GetPhysicalRequestByID(DataBase dataBase, DbTransaction tran, string physicalrequestID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_PHYSICAL_REQUEST.Replace("@_@", whLoginID) + " WHERE PHYSICAL_REQUEST_ID = @PHYSICAL_REQUEST_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PHYSICAL_REQUEST_ID", physicalrequestID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            PhysicalRequestInfo physicalRequestInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                DateTime CS00003;
                physicalRequestInfo = new PhysicalRequestInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["PHYSICAL_REQUEST_ID"].ToString(), reader["DESCR"].ToString(), reader["OWNER_ID_START"].ToString(), reader["OWNER_ID_END"].ToString(), reader["SKU_ID_START"].ToString(), reader["SKU_ID_END"].ToString(), reader["LOC_START"].ToString(), reader["LOC_END"].ToString(), reader["ZONE_ID_START"].ToString(), reader["ZONE_ID_END"].ToString(), reader["AREA_ID_START"].ToString(), reader["AREA_ID_END"].ToString(), (reader["LOC_LEVEL_START"].ToString() != "") ? int.Parse(reader["LOC_LEVEL_START"].ToString()) : 0, (reader["LOC_LEVEL_END"].ToString() != "") ? int.Parse(reader["LOC_LEVEL_END"].ToString()) : 0, reader["SHOW_SYSTEM_QTY"].ToString(), (reader["STARTING_TAG_NO"].ToString() != "") ? int.Parse(reader["STARTING_TAG_NO"].ToString()) : 0, (reader["TAGS_PER_GROUP"].ToString() != "") ? int.Parse(reader["TAGS_PER_GROUP"].ToString()) : 0, reader["ONLY_NON_EMPTY_LOC"].ToString(), (reader["PLAN_START_TIME"].ToString() != "") ? DateTime.Parse(reader["PLAN_START_TIME"].ToString()) : (CS00003 = new DateTime()), (reader["PLAN_END_TIME"].ToString() != "") ? DateTime.Parse(reader["PLAN_END_TIME"].ToString()) : (CS00003 = new DateTime()), reader["STATUS"].ToString(), reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime(), reader["STATUS_D"].ToString());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return physicalRequestInfo;
        }

        public DataSet GetPhysicalRequestByQueryList(List<string> sqlWhere, PhysicalRequestQueryEntity physicalrequestQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_PHYSICAL_REQUEST.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!physicalrequestQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, physicalrequestQuery.CurrentPage, physicalrequestQuery.PageSize, physicalrequestQuery.SortField, physicalrequestQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'");
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetPhysicalRequestByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, PhysicalRequestQueryEntity physicalrequestQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_PHYSICAL_REQUEST.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage) };
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!physicalrequestQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, physicalrequestQuery.CurrentPage, physicalrequestQuery.PageSize, physicalrequestQuery.SortField, physicalrequestQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'");
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp, paras);
        }

        public int InsertPhysicalRequest(PhysicalRequestInfo physicalRequestInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                physicalRequestInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_PhysicalRequest_Parameters(physicalRequestInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_PHYSICAL_REQUEST.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                conn.Close();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int InsertPhysicalRequest(DataBase dataBase, DbTransaction tran, PhysicalRequestInfo physicalRequestInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                physicalRequestInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_PhysicalRequest_Parameters(physicalRequestInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_PHYSICAL_REQUEST.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public List<PhysicalCountInventory> SelectInventoryByPhysicalRequest(DataBase dataBase, DbTransaction tran, List<string> SqlWhere, string UserID, string whLoginID)
        {
            string strSql = this.SQL_SELECT_INV_LOT_LOC_LPN_BY_PHYSICAL_REQUEST.Replace("@_@", whLoginID).Replace("@USER_ID", UserID);
            for (int i = 0; i < SqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    strSql = strSql + " WHERE " + SqlWhere[i].ToString();
                }
                else
                {
                    strSql = strSql + " AND " + SqlWhere[i].ToString();
                }
            }
            strSql = strSql + " ORDER BY INV.LOC ,INV.SKU_ID,INV.LOT ASC ";
            List<PhysicalCountInventory> physicalCountInventoryinfoList = new List<PhysicalCountInventory>();
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, strSql);
            while (reader.Read())
            {
                DateTime CS00002;
                PhysicalCountInventory physicalCountInventoryinfo = new PhysicalCountInventory((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["LOT"].ToString(), reader["LOC"].ToString(), reader["LPN_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), (reader["QTY"].ToString() != "") ? decimal.Parse(reader["QTY"].ToString()) : 0M, (reader["ALLOCATED_QTY"].ToString() != "") ? decimal.Parse(reader["ALLOCATED_QTY"].ToString()) : 0M, (reader["PICKED_QTY"].ToString() != "") ? decimal.Parse(reader["PICKED_QTY"].ToString()) : 0M, (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["PICKINPROCESS_QTY"].ToString() != "") ? decimal.Parse(reader["PICKINPROCESS_QTY"].ToString()) : 0M, (reader["PENDING_MOVE_IN"].ToString() != "") ? decimal.Parse(reader["PENDING_MOVE_IN"].ToString()) : 0M, (reader["ARCHIVE_QTY"].ToString() != "") ? decimal.Parse(reader["ARCHIVE_QTY"].ToString()) : 0M, (reader["ARCHIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["ARCHIVE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["STATUS"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["PACK_ID"].ToString(), reader["PACK_UOM1"].ToString());
                physicalCountInventoryinfoList.Add(physicalCountInventoryinfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return physicalCountInventoryinfoList;
        }

        private SqlParameter[] Set_PhysicalRequest_Parameters(PhysicalRequestInfo physicalRequestInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.PHYSICAL_REQUEST_ID, SqlDbType.VarChar, 20), new SqlParameter(this.DESCR, SqlDbType.VarChar, 100), new SqlParameter(this.OWNER_ID_START, SqlDbType.VarChar, 20), new SqlParameter(this.OWNER_ID_END, SqlDbType.VarChar, 20), new SqlParameter(this.SKU_ID_START, SqlDbType.VarChar, 50), new SqlParameter(this.SKU_ID_END, SqlDbType.VarChar, 50), new SqlParameter(this.LOC_START, SqlDbType.VarChar, 20), new SqlParameter(this.LOC_END, SqlDbType.VarChar, 20), new SqlParameter(this.ZONE_ID_START, SqlDbType.VarChar, 20), new SqlParameter(this.ZONE_ID_END, SqlDbType.VarChar, 20), new SqlParameter(this.AREA_ID_START, SqlDbType.VarChar, 20), new SqlParameter(this.AREA_ID_END, SqlDbType.VarChar, 20), new SqlParameter(this.LOC_LEVEL_START, SqlDbType.Int, 4), new SqlParameter(this.LOC_LEVEL_END, SqlDbType.Int, 4), new SqlParameter(this.SHOW_SYSTEM_QTY, SqlDbType.VarChar, 1), 
                new SqlParameter(this.STARTING_TAG_NO, SqlDbType.Int, 4), new SqlParameter(this.TAGS_PER_GROUP, SqlDbType.Int, 4), new SqlParameter(this.ONLY_NON_EMPTY_LOC, SqlDbType.VarChar, 1), new SqlParameter(this.PLAN_START_TIME, SqlDbType.DateTime, 8), new SqlParameter(this.PLAN_END_TIME, SqlDbType.DateTime, 8), new SqlParameter(this.STATUS, SqlDbType.VarChar, 20), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20)
             };
            if (!string.IsNullOrEmpty(physicalRequestInfo.WhID))
            {
                paramArray[0].Value = physicalRequestInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = physicalRequestInfo.PhysicalRequestID;
            if (!string.IsNullOrEmpty(physicalRequestInfo.Descr))
            {
                paramArray[2].Value = physicalRequestInfo.Descr;
            }
            else
            {
                paramArray[2].Value = DBNull.Value;
            }
            paramArray[3].Value = physicalRequestInfo.OwnerIdStart;
            paramArray[4].Value = physicalRequestInfo.OwnerIdEnd;
            paramArray[5].Value = physicalRequestInfo.SkuIdStart;
            paramArray[6].Value = physicalRequestInfo.SkuIdEnd;
            paramArray[7].Value = physicalRequestInfo.LocStart;
            paramArray[8].Value = physicalRequestInfo.LocEnd;
            paramArray[9].Value = physicalRequestInfo.ZoneIdStart;
            paramArray[10].Value = physicalRequestInfo.ZoneIdEnd;
            paramArray[11].Value = physicalRequestInfo.AreaIdStart;
            paramArray[12].Value = physicalRequestInfo.AreaIdEnd;
            paramArray[13].Value = physicalRequestInfo.LocLevelStart;
            paramArray[14].Value = physicalRequestInfo.LocLevelEnd;
            paramArray[15].Value = physicalRequestInfo.ShowSystemQty;
            paramArray[0x10].Value = physicalRequestInfo.StartingTagNo;
            paramArray[0x11].Value = physicalRequestInfo.TagsPerGroup;
            paramArray[0x12].Value = physicalRequestInfo.OnlyNonEmptyLoc;
            paramArray[0x13].Value = physicalRequestInfo.PlanStartTime;
            paramArray[20].Value = physicalRequestInfo.PlanEndTime;
            paramArray[0x15].Value = physicalRequestInfo.Status;
            paramArray[0x16].Value = physicalRequestInfo.CreatedBy;
            paramArray[0x17].Value = physicalRequestInfo.UpdatedBy;
            return paramArray;
        }

        public int UpdatePhysicalRequest(PhysicalRequestInfo physicalRequestInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                physicalRequestInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_PhysicalRequest_Parameters(physicalRequestInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_PHYSICAL_REQUEST.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdatePhysicalRequest(DataBase dataBase, DbTransaction tran, PhysicalRequestInfo physicalRequestInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                physicalRequestInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_PhysicalRequest_Parameters(physicalRequestInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_PHYSICAL_REQUEST.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdatePhysicalRequestStatus(string physicalRequestID, string Status, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PHYSICAL_REQUEST_ID", physicalRequestID), new SqlParameter("@STATUS", Status) };
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_PHYSICAL_REQUEST_STATUS_BY_PHYSICAL_REQUEST_ID.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdatePhysicalRequestStatus(DataBase dataBase, DbTransaction tran, string physicalRequestID, string Status, string whLoginID)
        {
            int result = 0;
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PHYSICAL_REQUEST_ID", physicalRequestID), new SqlParameter("@STATUS", Status) };
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_PHYSICAL_REQUEST_STATUS_BY_PHYSICAL_REQUEST_ID.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

